package com.rzy.cqh.dao;

import java.sql.*;
import java.text.*;
import java.util.*;

import org.apache.log4j.Logger;

public class GeneralDAO {
  private static final Logger log = Logger.getLogger(GeneralDAO.class);

  private static String driver;
  private static String url;
  private static String user;
  private static String pwd;

  GeneralDAO() {
  }

  static {
    Properties pp = new Properties();
    try {
      pp.load(GeneralDAO.class.getResourceAsStream("/jdbc.properties"));

      driver = pp.getProperty("jdbc.driver");
      url = pp.getProperty("jdbc.url");
      user = pp.getProperty("jdbc.username");
      pwd = pp.getProperty("jdbc.password");

      log.debug("GeneralDAO driver:" + driver);
      log.debug("GeneralDAO url:" + url);
      log.debug("GeneralDAO user:" + user);

      Class.forName(driver);
    } catch (Exception e) {
      log.error("GeneralDAO init error.", e);
    }
  }

  public static String getUUID() {
    return UUID.randomUUID().toString().replaceAll("-", "");
  }

  private static Connection getConnection() throws SQLException {
    return DriverManager.getConnection(url, user, pwd);
  }

  private static void closeConnection(Connection conn) {
    try {
      if (conn != null) {
        conn.close();
      }
    } catch (Exception e) {
      log.error("GeneralDAO.closeConnection() error.", e);
    }
  }

  static List<Map<String, String>> query(ResultSet rs) throws SQLException {
    List<Map<String, String>> result = new ArrayList<Map<String, String>>();
    ResultSetMetaData md = rs.getMetaData();

    int count = md.getColumnCount();

    while (rs.next()) {
      Map<String, String> map = new HashMap<String, String>();
      for (int i = 1; i <= count; i++) {
        String name = md.getColumnName(i).toUpperCase();
        int type = md.getColumnType(i);
        if (type == Types.DATE || type == Types.TIME || type == Types.TIMESTAMP) {
          SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
          if (rs.getTimestamp(i) != null) {
            map.put(name.toUpperCase(), fmt.format(rs.getTimestamp(i)));
            map.put(name.toLowerCase(), fmt.format(rs.getTimestamp(i)));
          }
        } else {
          map.put(name.toUpperCase(), rs.getString(i));
          map.put(name.toLowerCase(), rs.getString(i));
        }
      }
      result.add(map);
    }
    return result;
  }

  public static List<Map<String, String>> query(String sql) {
    return query(sql, new String[] {});
  }

  public static Map<String, String> queryOne(String sql) {
    List<Map<String, String>> result = query(sql);
    if (result.size() == 1)
      return result.get(0);

    return null;
  }

  public static List<Map<String, String>> query(String sql, List<String> args) {
    if (args == null || args.size() == 0) {
      return query(sql);
    }
    String[] _args = (String[]) args.toArray(new String[args.size()]);
    return query(sql, _args);
  }

  public static Map<String, String> queryOne(String sql, List<String> args) {
    List<Map<String, String>> result = query(sql, args);
    if (result.size() == 1)
      return result.get(0);

    return null;
  }

  public static List<Map<String, String>> query(String sql, String[] args) {
    String pp = "";
    for (String s : args) {
      pp += s + ",";
    }
    if (pp.length() > 0) {
      pp = pp.substring(0, pp.length() - 1);
    }
    log.debug("[" + sql + " - " + pp + "]");

    List<Map<String, String>> result = null;
    Connection conn = null;
    try {
      conn = getConnection();

      PreparedStatement st = conn.prepareStatement(sql);
      for (int i = 0; i < args.length; i++) {
        st.setString(i + 1, args[i]);
      }
      ResultSet rs = st.executeQuery();
      result = query(rs);
      rs.close();
      st.close();
    } catch (Exception e) {
      log.error("GeneralDAO.query(String sql, String[] args) error.", e);
    } finally {
      closeConnection(conn);
    }

    return result;
  }

  public static Map<String, String> queryOne(String sql, String[] args) {
    List<Map<String, String>> result = query(sql, args);
    if (result.size() == 1)
      return result.get(0);

    return null;
  }

  public static int execute(String sql) {
    return execute(sql, new String[] {});
  }

  public static int execute(String sql, List<String> args) {
    if (args == null || args.size() == 0) {
      return execute(sql);
    }
    String[] _args = (String[]) args.toArray(new String[args.size()]);
    return execute(sql, _args);
  }

  public static int execute(String sql, String[] args) {
    String pp = "";
    for (String s : args) {
      pp += s + ",";
    }
    if (pp.length() > 0) {
      pp = pp.substring(0, pp.length() - 1);
    }
    log.debug("[" + sql + " - " + pp + "]");

    int result = 0;
    Connection conn = null;
    try {
      conn = getConnection();

      PreparedStatement st = conn.prepareStatement(sql);
      int i = 1;
      for (String s : args) {
        st.setString(i, s);
        i++;
      }
      result = st.executeUpdate();
      st.close();
    } catch (Exception e) {
      log.error("GeneralDAO.execute(String sql, String[] args) error.", e);
    } finally {
      closeConnection(conn);
    }
    return result;
  }
}
